C# WebAPI Transactions

This is a continuation from the CSharp WebApi - a quick refresher writeup.

• Step 1: Modify the Database Context
• Step 2: Create a New Migration
• Step 3: Update the API for Transactional Data Insert
• Step 4: Test the New Endpoint

WebAPI Transactions

In this article we will add another string to the mix, and both strings must be saved in single transaction or neither should be saved at all (Transactional).

Step 1: Modify the Database Context

Update your StringContext to include a new table. For example, let’s add another model AnotherData:

Notice the two comments to add the 1. Getter/Setter and 2. the new Class:

using Microsoft.EntityFrameworkCore;

namespace StringToDbApi
{
    public class StringContext : DbContext
    {
        public DbSet<StringData> Strings { get; set; }
        public DbSet<AnotherData> Others { get; set; } // New DbSet for the second table

        public StringContext(DbContextOptions<StringContext> options)
            : base(options)
        {
        }
    }

    public class StringData
    {
        public int Id { get; set; }
        public string Content { get; set; }
    }

	// New class for the Dataset
    public class AnotherData
    {
        public int Id { get; set; }
        public string Description { get; set; } // New table field
    }
}

Step 2: Create a New Migration

Since we've added a new table, we need to update the database schema:

dotnet ef migrations add AddAnotherTable
dotnet ef database update

Output of first command:

And the output of the next command:

uild started...
Build succeeded.
The Entity Framework tools version '8.0.8' is older than that of the runtime '9.0.0'. Update the tools for the latest features and bug fixes. See https://aka.ms/AAc1fbw for more information.
info: Microsoft.EntityFrameworkCore.Migrations[20411]
      Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsLock' AND "type" = 'table';
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT OR IGNORE INTO "__EFMigrationsLock"("Id", "Timestamp") VALUES(1, '2024-11-25 07:08:53.268124+00:00');
.
.
.

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
      VALUES ('20241125070807_AddAnotherTable', '9.0.0');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DELETE FROM "__EFMigrationsLock";
Done.

Step 3: Update the API for Transactional Data Insert

Modify the Program.cs file to include a new endpoint that inserts into both tables in a single transaction:

Let's add a new end-point with the capability to add both string values (each has its own table indeed) wrapped up in a single transactions:

app.MapPost("/save-transaction", async (StringData stringData, AnotherData anotherData, StringContext context) =>
{
    using var transaction = await context.Database.BeginTransactionAsync();

    try
    {
        // Add to first table
        context.Strings.Add(stringData);
        await context.SaveChangesAsync();

        // Add to second table
        context.Others.Add(anotherData);
        await context.SaveChangesAsync();

        // Commit transaction
        await transaction.CommitAsync();

        return Results.Created("/save-transaction", new { stringData, anotherData });
    }
    catch
    {
        // Rollback transaction on failure
        await transaction.RollbackAsync();
        return Results.Problem("An error occurred while saving data.");
    }
});

• BeginTransactionAsync: Starts a database transaction.
• Changes to both tables (Strings and Others) are made within the same transaction.
• If an error occurs, the transaction is rolled back to ensure data consistency.
• If both were successful, CommitAsync() will commit the data to the SQLite database.

Step 4: Test the New Endpoint

Let's run the application:

dotnet run

Test with both parameters:

curl -X POST http://localhost:5154/save-transaction -H "Content-Type: application/json" -d '{
    "stringData": { "content": "Transaction String" },
    "anotherData": { "description": "Transaction Description" }
}'